Constraints
In SQL, constraints are rules applied to columns in a table to ensure the integrity, accuracy, and reliability of the data within the database. Constraints help enforce data rules and maintain data quality by restricting the types of data that can be stored in a table. Here are the common types of constraints in SQL, explained in detail:
The NOT NULL constraint ensures that a column cannot have a NULL value. This constraint enforces that every row must have a value for the specified column.
CREATE TABLE table_name (
column_name datatype NOT NULL,
...
);
CREATE TABLE employees (
employee_id INT NOT NULL,
first_name VARCHAR(50) NOT NULL,
last_name VARCHAR(50) NOT NULL,
birth_date DATE
);
In this example, the employee_id, first_name, and last_name columns cannot contain NULL values.
The UNIQUE constraint ensures that all the values in a column are different. It prevents duplicate values in the specified column or combination of columns.
CREATE TABLE table_name (
column_name datatype UNIQUE,
...
);
CREATE TABLE employees (
employee_id INT UNIQUE,
email VARCHAR(100) UNIQUE,
first_name VARCHAR(50),
last_name VARCHAR(50)
);
In this example, the employee_id and email columns must have unique values, meaning no two rows can have the same employee_id or email.
The PRIMARY KEY constraint uniquely identifies each record in a table. A table can have only one primary key, which can consist of single or multiple columns. The columns included in a primary key cannot contain NULL values.
CREATE TABLE table_name (
column_name datatype PRIMARY KEY,
...
);
CREATE TABLE employees (
employee_id INT PRIMARY KEY,
first_name VARCHAR(50),
last_name VARCHAR(50),
email VARCHAR(100) UNIQUE
);
In this example, the employee_id column is the primary key, which uniquely identifies each employee.
The FOREIGN KEY constraint ensures referential integrity by establishing a link between the data in two tables. It ensures that the value in a column (or a group of columns) matches the value in the primary key column of another table.
CREATE TABLE table_name (
column_name datatype,
...
FOREIGN KEY (column_name) REFERENCES other_table_name (primary_key_column)
);
CREATE TABLE departments (
department_id INT PRIMARY KEY,
department_name VARCHAR(50)
);
CREATE TABLE employees (
employee_id INT PRIMARY KEY,
first_name VARCHAR(50),
last_name VARCHAR(50),
department_id INT,
FOREIGN KEY (department_id) REFERENCES departments(department_id)
);
In this example, the department_id column in the employees table is a foreign key that references the department_id column in the departments table.
The CHECK constraint ensures that all values in a column satisfy a specific condition. This constraint allows you to define custom rules for the data in the table.
CREATE TABLE table_name (
column_name datatype CHECK (condition),
...
);
CREATE TABLE employees (
employee_id INT PRIMARY KEY,
first_name VARCHAR(50),
last_name VARCHAR(50),
salary DECIMAL(10, 2) CHECK (salary > 0),
birth_date DATE CHECK (birth_date > '1900-01-01')
);
In this example, the salary column must have a value greater than 0, and the birth_date column must have a value later than January 1, 1900.
The DEFAULT constraint provides a default value for a column when no value is specified during the insertion of a new record.
CREATE TABLE table_name (
column_name datatype DEFAULT default_value,
...
);
CREATE TABLE employees (
employee_id INT PRIMARY KEY,
first_name VARCHAR(50),
last_name VARCHAR(50),
hire_date DATE DEFAULT CURRENT_DATE
);
In this example, if no value is provided for the hire_date column when a new employee is added, the current date will be used as the default value.
A composite primary key is a primary key that consists of two or more columns. This type of key ensures that the combination of values in the specified columns is unique across the table.
CREATE TABLE table_name (
column1 datatype,
column2 datatype,
...
PRIMARY KEY (column1, column2)
);
CREATE TABLE order_details (
order_id INT,
product_id INT,
quantity INT,
PRIMARY KEY (order_id, product_id)
);
In this example, the combination of order_id and product_id uniquely identifies each row in the order_details table.
The CREATE statement in SQL is used to create a new database, table, index, or other objects in a database. Here, I'll provide a detailed explanation of how to use the CREATE statement for these different objects.
To create a new database, you use the CREATE DATABASE statement.
CREATE DATABASE database_name;
CREATE DATABASE company_db;
This command creates a new database named company_db.
To create a new table within a database, you use the CREATE TABLE statement. This involves specifying the table name and defining its columns along with their data types and constraints.
CREATE TABLE table_name (
column1 datatype constraint,
column2 datatype constraint,
...
);
CREATE TABLE employees (
employee_id INT PRIMARY KEY,
first_name VARCHAR(50) NOT NULL,
last_name VARCHAR(50) NOT NULL,
birth_date DATE,
hire_date DATE,
salary DECIMAL(10, 2),
department_id INT
);
The SELECT statement in SQL is used to retrieve data from one or more tables in a database. It is one of the most fundamental and widely used SQL operations. Here is a detailed explanation of the SELECT statement:
SELECT column1, column2, ... FROM table_name;
Selecting Specific Columns
SELECT first_name, last_name FROM employees;
This query retrieves the first_name and last_name columns from the employees table.
Selecting All Columns
SELECT * FROM employees;
This query retrieves all columns from the employees table.
The WHERE clause is used to filter records that meet certain criteria.
SELECT column1, column2, ...
FROM table_name
WHERE condition;
SELECT first_name, last_name
FROM employees
WHERE department = 'Sales';
This query retrieves the first_name and last_name of employees who work in the Sales department.
You can use logical operators (AND, OR, NOT) to combine multiple conditions.
SELECT first_name, last_name
FROM employees
WHERE department = 'Sales' AND salary > 50000;
This query retrieves the first_name and last_name of employees who work in the Sales department and have a salary greater than 50,000.
The ORDER BY clause is used to sort the result set.
SELECT column1, column2, ...
FROM table_name
ORDER BY column1, column2, ... ASC|DESC;
ASC is for ascending order (default).DESC is for descending order.SELECT first_name, last_name
FROM employees
ORDER BY last_name ASC;
This query retrieves the first_name and last_name of employees, sorted by last_name in ascending order.
The LIMIT clause is used to specify the number of records to return.
SELECT column1, column2, ...
FROM table_name
LIMIT number;
SELECT first_name, last_name
FROM employees
LIMIT 10;
This query retrieves the first 10 records from the employees table.
Aliases are used to give a table or a column a temporary name.
SELECT column1 AS alias_name, column2
FROM table_name AS alias_name;
SELECT first_name AS fname, last_name AS lname
FROM employees AS e;
This query retrieves the first_name and last_name from the employees table, but the result set will have columns named fname and lname.
The GROUP BY clause groups rows that have the same values into summary rows.
SELECT column1, COUNT(*)
FROM table_name
GROUP BY column1;
SELECT department, COUNT(*)
FROM employees
GROUP BY department;
This query retrieves the number of employees in each department.
The HAVING clause is used to filter groups based on a condition.
SELECT column1, COUNT(*)
FROM table_name
GROUP BY column1
HAVING COUNT(*) > number;
SELECT department, COUNT(*)
FROM employees
GROUP BY department
HAVING COUNT(*) > 5;
This query retrieves departments with more than 5 employees.
In SQL, joins are used to combine rows from two or more tables based on a related column between them. Joins allow you to retrieve data from multiple tables as if they were a single table. There are several types of joins, each serving a different purpose. Here’s a detailed explanation of the most common types of joins:
An INNER JOIN returns only the rows that have matching values in both tables. If there is no match, the row is excluded from the result set.
SELECT columns
FROM table1
INNER JOIN table2
ON table1.column = table2.column;
SELECT employees.first_name, employees.last_name, departments.department_name
FROM employees
INNER JOIN departments
ON employees.department_id = departments.department_id;
This query returns the first_name and last_name of employees along with the department_name from the departments where there is a match between employees.department_id and departments.department_id.
A LEFT JOIN returns all rows from the left table (table1) and the matched rows from the right table (table2). If there is no match, the result is NULL on the side of the right table.
SELECT columns
FROM table1
LEFT JOIN table2
ON table1.column = table2.column;
SELECT employees.first_name, employees.last_name, departments.department_name
FROM employees
LEFT JOIN departments
ON employees.department_id = departments.department_id;
This query returns all employees, and their corresponding department names. If an employee does not belong to any department, the department_name will be NULL.
A RIGHT JOIN returns all rows from the right table (table2) and the matched rows from the left table (table1). If there is no match, the result is NULL on the side of the left table.
SELECT columns
FROM table1
RIGHT JOIN table2
ON table1.column = table2.column;
SELECT employees.first_name, employees.last_name, departments.department_name
FROM employees
RIGHT JOIN departments
ON employees.department_id = departments.department_id;
This query returns all departments, and the employees that belong to them. If a department does not have any employees, the first_name and last_name will be NULL.
A FULL JOIN returns all rows when there is a match in either the left table (table1) or the right table (table2). If there is no match, the result is NULL for the columns of the table that lacks a matching row.
SELECT columns
FROM table1
FULL JOIN table2
ON table1.column = table2.column;
SELECT employees.first_name, employees.last_name, departments.department_name
FROM employees
FULL JOIN departments
ON employees.department_id = departments.department_id;
This query returns all employees and all departments. If an employee does not belong to a department, the department_name will be NULL. If a department does not have any employees, the first_name and last_name will be NULL.
A CROSS JOIN returns the Cartesian product of the two tables, meaning it combines all rows from the first table with all rows from the second table.
SELECT columns
FROM table1
CROSS JOIN table2;
SELECT employees.first_name, departments.department_name
FROM employees
CROSS JOIN departments;
This query returns every combination of first_name from employees and department_name from departments.
A SELF JOIN is a regular join, but the table is joined with itself. This is useful for querying hierarchical data or comparing rows within the same table.
SELECT a.column1, b.column2
FROM table_name a, table_name b
WHERE condition;
SELECT e1.first_name AS Employee, e2.first_name AS Manager
FROM employees e1
INNER JOIN employees e2
ON e1.manager_id = e2.employee_id;
This query returns employees and their managers from the employees table.
Using table aliases (short names for table names) in joins can make your SQL queries more readable, especially when dealing with complex queries.
SELECT e.first_name, e.last_name, d.department_name
FROM employees e
INNER JOIN departments d
ON e.department_id = d.department_id;
In this example, e and d are aliases for the employees and departments tables, respectively.
Aggregate functions in SQL are used to perform calculations on a set of values and return a single value. These functions are often used with the GROUP BY clause to group the result set by one or more columns. Here are the most commonly used aggregate functions:
The COUNT() function returns the number of rows that match a specified condition.
SELECT COUNT(column_name)
FROM table_name
WHERE condition;
SELECT COUNT(employee_id) AS number_of_employees
FROM employees
WHERE department_id = 1;
This query returns the number of employees in the department with department_id 1.
The SUM() function returns the total sum of a numeric column.
SELECT SUM(column_name)
FROM table_name
WHERE condition;
SELECT SUM(salary) AS total_salary
FROM employees
WHERE department_id = 1;
This query returns the total salary of all employees in the department with department_id 1.
The AVG() function returns the average value of a numeric column.
SELECT AVG(column_name)
FROM table_name
WHERE condition;
SELECT AVG(salary) AS average_salary
FROM employees
WHERE department_id = 1;
This query returns the average salary of employees in the department with department_id 1.
The MIN() function returns the smallest value in a column.
SELECT MIN(column_name)
FROM table_name
WHERE condition;
SELECT MIN(salary) AS lowest_salary
FROM employees
WHERE department_id = 1;
This query returns the lowest salary of employees in the department with department_id 1.
The MAX() function returns the largest value in a column.
SELECT MAX(column_name)
FROM table_name
WHERE condition;
SELECT MAX(salary) AS highest_salary
FROM employees
WHERE department_id = 1;
This query returns the highest salary of employees in the department with department_id 1.
The GROUP BY clause is used with aggregate functions to group the result set by one or more columns.
SELECT column1, aggregate_function(column2)
FROM table_name
WHERE condition
GROUP BY column1;
SELECT department_id, COUNT(employee_id) AS number_of_employees
FROM employees
GROUP BY department_id;
This query returns the number of employees in each department.
The HAVING clause is used to filter groups based on a specified condition, often used with GROUP BY.
SELECT column1, aggregate_function(column2)
FROM table_name
WHERE condition
GROUP BY column1
HAVING condition;
SELECT department_id, COUNT(employee_id) AS number_of_employees
FROM employees
GROUP BY department_id
HAVING COUNT(employee_id) > 5;
This query returns only the departments with more than 5 employees.
You can use multiple aggregate functions in a single query to perform different calculations on different columns.
SELECT department_id,
COUNT(employee_id) AS number_of_employees,
AVG(salary) AS average_salary,
MIN(salary) AS lowest_salary,
MAX(salary) AS highest_salary
FROM employees
GROUP BY department_id;
This query returns the number of employees, average salary, lowest salary, and highest salary for each department.
Consider a sales table with the following columns: sale_id, product_id, quantity, price, sale_date.
SELECT SUM(quantity * price) AS total_sales
FROM sales;
This query calculates the total sales revenue.
SELECT product_id, SUM(quantity) AS total_quantity_sold, SUM(quantity * price) AS total_revenue
FROM sales
GROUP BY product_id;
This query returns the total quantity sold and total revenue for each product.
SELECT AVG(quantity * price) AS average_sale_amount
FROM sales;
This query calculates the average amount of each sale.
In SQL, a view is a virtual table based on the result set of a SELECT query. A view does not store the data itself, but rather it provides a way to represent and access data from one or more tables. Views are used to simplify complex queries, enhance security, and present data in a specific format.
The CREATE VIEW statement is used to create a view.
CREATE VIEW view_name AS
SELECT columns
FROM table_name
WHERE condition;
CREATE VIEW employee_info AS
SELECT first_name, last_name, department_id
FROM employees
WHERE hire_date > '2020-01-01';
This command creates a view named employee_info that contains the first_name, last_name, and department_id of employees hired after January 1, 2020.
You can query a view just like a table.
SELECT * FROM employee_info;
This query retrieves all rows and columns from the employee_info view.
Depending on the complexity of the view and the database system, you can sometimes use INSERT, UPDATE, and DELETE statements on a view. However, not all views are updatable.
UPDATE employee_info
SET department_id = 5
WHERE last_name = 'Doe';
This query updates the department_id for employees with the last name 'Doe' in the employee_info view.
To remove a view from the database, use the DROP VIEW statement.
DROP VIEW view_name;
DROP VIEW employee_info;
This command deletes the employee_info view.
Simple views are based on a single table and do not include group functions, such as SUM or AVG, or join multiple tables.
CREATE VIEW employee_names AS
SELECT first_name, last_name
FROM employees;
Complex views can be based on multiple tables and can include group functions, joins, and subqueries.
CREATE VIEW department_summary AS
SELECT d.department_name, COUNT(e.employee_id) AS num_employees, AVG(e.salary) AS avg_salary
FROM departments d
LEFT JOIN employees e
ON d.department_id = e.department_id
GROUP BY d.department_name;
This view provides a summary of each department, including the number of employees and the average salary.
INSERT, UPDATE, or DELETE operations.Materialized views are similar to regular views, but they store the result set of the query physically, which can improve query performance for complex and time-consuming operations. However, materialized views require periodic refreshes to stay up-to-date.
CREATE MATERIALIZED VIEW view_name AS
SELECT columns
FROM table_name
WHERE condition;
CREATE MATERIALIZED VIEW sales_summary AS
SELECT product_id, SUM(quantity) AS total_quantity
FROM sales
GROUP BY product_id;
This creates a materialized view that stores the total quantity sold for each product.
Transactions in SQL are a sequence of one or more SQL statements that are executed as a single unit of work. They ensure data integrity and consistency by following the ACID properties: Atomicity, Consistency, Isolation, and Durability.
BEGIN TRANSACTION;
UPDATE accounts
SET balance = balance - 100
WHERE account_id = 1;
UPDATE accounts
SET balance = balance + 100
WHERE account_id = 2;
IF @@ERROR <> 0
BEGIN
ROLLBACK;
PRINT 'Transaction failed and was rolled back';
END
ELSE
BEGIN
COMMIT;
PRINT 'Transaction committed successfully';
END;
A nested SELECT, also known as a subquery, is a query within another SQL query. Subqueries can be used in various clauses like SELECT, FROM, WHERE, and HAVING.
SELECT employee_id, first_name, last_name,
(SELECT department_name
FROM departments
WHERE departments.department_id = employees.department_id) AS department_name
FROM employees;
This query returns employee details along with their department names.
SELECT first_name, last_name
FROM employees
WHERE department_id =
(SELECT department_id
FROM departments
WHERE department_name = 'Sales');
This query returns the first and last names of employees who work in the 'Sales' department.
SELECT department_id, AVG(salary) AS avg_salary
FROM (SELECT department_id, salary
FROM employees) AS emp_salaries
GROUP BY department_id;
This query calculates the average salary for each department.
An index in SQL is a database object that improves the speed of data retrieval operations on a table at the cost of additional writes and storage space to maintain the index data structure. Indexes are created on columns to allow faster searches and improve query performance.
CREATE INDEX index_name
ON table_name (column1, column2, ...);
CREATE INDEX idx_employee_last_name
ON employees (last_name);
This creates an index on the last_name column of the employees table.
CREATE INDEX idx_employee_name
ON employees (last_name, first_name);
This creates an index on both the last_name and first_name columns of the employees table.
CREATE UNIQUE INDEX idx_unique_email
ON employees (email);
This creates a unique index on the email column of the employees table, ensuring no duplicate email addresses.
DROP INDEX index_name;
DROP INDEX idx_employee_last_name;
This command drops the idx_employee_last_name index from the employees table.
Triggers in SQL are special types of stored procedures that automatically execute in response to certain events on a particular table or view in a database. These events can include INSERT, UPDATE, DELETE operations, or a combination thereof. Triggers are used to enforce business rules, validate data changes, maintain data integrity, and automate tasks that need to occur alongside database operations.
CREATE [OR REPLACE] TRIGGER trigger_name
{BEFORE | AFTER} {INSERT | UPDATE | DELETE} ON table_name
[FOR EACH ROW] -- For row-level triggers; omit for statement-level triggers
BEGIN
-- Trigger logic goes here
END;
CREATE OR REPLACE TRIGGER before_insert_employee
BEFORE INSERT ON employees
FOR EACH ROW
BEGIN
IF :NEW.salary < 0 THEN
RAISE_APPLICATION_ERROR(-20001, 'Salary cannot be negative');
END IF;
END;
In this example:
before_insert_employee is the name of the trigger.BEFORE INSERT ON employees specifies that the trigger will fire before an insert operation on the employees table.FOR EACH ROW indicates that this is a row-level trigger, meaning it will execute for each row affected by the insert operation.:NEW.salary refers to the value of the salary column being inserted.RAISE_APPLICATION_ERROR is used to raise an error if the inserted salary is negative.CREATE OR REPLACE TRIGGER after_update_inventory
AFTER UPDATE ON inventory
FOR EACH ROW
BEGIN
INSERT INTO inventory_logs (item_id, old_quantity, new_quantity, change_date)
VALUES (:OLD.item_id, :OLD.quantity, :NEW.quantity, SYSDATE);
END;
In this example:
after_update_inventory is the name of the trigger.AFTER UPDATE ON inventory specifies that the trigger will fire after an update operation on the inventory table.:OLD.item_id and :NEW.quantity refer to the values of the item_id and quantity columns before and after the update, respectively.SYSDATE is a built-in function in SQL that returns the current date and time.inventory_logs table whenever the quantity column in the inventory table is updated.To drop (delete) a trigger from a database, you use the DROP TRIGGER statement followed by the trigger name.
DROP TRIGGER trigger_name;